
--EXEC sprocePS_CarIsuranceResultSelectItemList 0, -1, 0, 0, 0, '', 'B', 0, 0, '', 0, 0, 2, 0, 1001, 3
ALTER PROCEDURE sprocePS_CarIsuranceResultSelectItemList 
@ScenarioId bigint
,@CXCoverTypeId bigint
,@ProviderCompanyId bigint
,@CarMakeId int
,@CarModelId bigint
,@DOB varchar(4)
,@Gender varchar(1)
,@MaritalStatus int
,@Occupation int
,@JobNature varchar(10)
,@DrivingExp int
,@ApplicableNCD int
,@IsClaim smallint
,@ClaimAmount int
,@AnnualPremiumAmount float
,@OrderBy smallint
,@IsFirstTime bit
AS
--Declare @ScenarioId bigint
--Declare @CXCoverTypeId bigint
--Declare @ProviderCompanyId bigint
--Declare @CarMakeId int
--Declare @CarModelId bigint
--Declare @DOB varchar(8)
--Declare	@Gender varchar(1)
--Declare @MaritalStatus int
--Declare @Occupation int
--Declare @JobNature varchar(10)
--Declare @DrivingExp int
--Declare @ApplicableNCD int
--Declare @IsClaim smallint
--Declare @ClaimAmount int

Declare @strSQL varchar(3500)
Declare @strWhere varchar(500)

--Set @ScenarioId = 0
--Set @CXCoverTypeId = -1
--Set @ProviderCompanyId = 0
--Set @CarMakeId = 802
--Set @CarModelId = 0
--Set @DOB = '19851010'
--Set @Gender = 'M'
--Set @MaritalStatus = 1
--Set @Occupation = 133
--Set @JobNature = 'INDOOR'
--Set @DrivingExp = 3
--Set @IsClaim = 1 --0 No Claim, 1 Yes Claim, 2 Both
--Set @ClaimAmount = 1500

--S.DOB, S.Gender, M.Description AS MaritalStaus, S.Occupation, S.JobNature, S.DrivingExp, S.ApplicableNCD, S.IsClaim, 
--                      S.ClaimAmount, PC.ProviderCompanyId, CR.CarMakeId, CR.CarModelId

Set @strSQL = 'SELECT S.ScenarioNo, CM.Description, CM.Body, CM.CC, PC.CompanyName, CT.CoverTypeDesc, PCC.ProviderCoverTypeDesc, CR.AnnualPremium, 
                      CR.Excess, S.ScenarioID
FROM         tblCarInsuranceResult AS CR INNER JOIN
                      tblScenario AS S ON CR.ScenarioId = S.ScenarioID INNER JOIN
                      tblCarModelInfo AS CM ON CR.CarMakeId = CM.CarMakeId AND CR.CarModelId = CM.CarModelId INNER JOIN
                      tblCoverTypeInfo AS CT ON CR.CXCoverTypeId = CT.CoverTypeId INNER JOIN
                      tblProviderCarCoverType AS PCC ON CR.ProviderCompanyId = PCC.ProviderCompanyId AND CR.CXCoverTypeId = PCC.CXCoverTypeId INNER JOIN
                      tblOccupationInfo AS O ON S.Occupation = O.OccupationId INNER JOIN
                      tblProviderCompany AS PC ON CR.ProviderCompanyId = PC.ProviderCompanyId INNER JOIN
                      tblMaritalStatusInfo AS M ON S.MaritalStatus = M.MaritalStatusId'

Set @strWhere = ' WHERE 1 = 1 '

IF @ScenarioId > 0
	BEGIN
		Set @strWhere = @strWhere + ' AND CR.ScenarioId = ' + CAST(@ScenarioId as varchar)
	END
	
IF @CXCoverTypeId > -1
	BEGIN
		Set @strWhere = @strWhere + ' AND CR.CXCoverTypeId = ' + CAST(@CXCoverTypeId as varchar)
	END

IF @ProviderCompanyId > 0
	BEGIN
		Set @strWhere = @strWhere + ' AND CR.ProviderCompanyId = ' + CAST(@ProviderCompanyId as varchar)
	END
	
IF @CarMakeId > 0
	BEGIN
		Set @strWhere = @strWhere + ' AND CR.CarMakeId = ' + CAST(@CarMakeId as varchar)
	END	

IF @CarModelId > 0
	BEGIN
		Set @strWhere = @strWhere + ' AND CR.CarModelId = ' + CAST(@CarModelId as varchar)
	END	

IF Len(@DOB) = 4
	BEGIN
		Set @strWhere = @strWhere + ' AND Convert(varchar(4), S.DOB, 112) = ''' + @DOB + ''''
	END	


IF @Gender <> 'B'
	BEGIN
		Set @strWhere = @strWhere + ' AND S.Gender = ''' + @Gender + ''''
	END	

IF @MaritalStatus > 0
	BEGIN
		Set @strWhere = @strWhere + ' AND S.MaritalStatus = ' + CAST(@MaritalStatus as varchar)
	END	

IF @Occupation > 0
	BEGIN
		Set @strWhere = @strWhere + ' AND S.Occupation = ' + CAST(@Occupation as varchar)
	END		

IF Len(@JobNature) > 0
	BEGIN
		Set @strWhere = @strWhere + ' AND S.JobNature = ''' + @JobNature + ''''
	END	

IF @DrivingExp > 0
	BEGIN
		Set @strWhere = @strWhere + ' AND S.DrivingExp = ' + CAST(@DrivingExp as varchar)
	END		

IF @DrivingExp > 0
	BEGIN
		Set @strWhere = @strWhere + ' AND S.DrivingExp = ' + CAST(@DrivingExp as varchar)
	END	
		
IF @ApplicableNCD > 0
	BEGIN
		Set @strWhere = @strWhere + ' AND S.ApplicableNCD = ' + CAST(@ApplicableNCD as varchar)
	END
			
IF @IsClaim = 1 And @ClaimAmount >= 0
	BEGIN
		Set @strWhere = @strWhere + ' AND S.IsClaim = 1 AND S.ClaimAmount = ' + CAST(@ClaimAmount as varchar)
	END	
ELSE IF @IsClaim = 0 
	BEGIN
		Set @strWhere = @strWhere + ' AND S.IsClaim = 0'
	END	

IF @AnnualPremiumAmount > 0
	BEGIN
		IF @AnnualPremiumAmount = 1
			BEGIN
				Set @strWhere = @strWhere + ' AND (CR.AnnualPremium Between 1 And 1000) ' 
			END
		ELSE IF @AnnualPremiumAmount = 1001
			BEGIN
				Set @strWhere = @strWhere + ' AND (CR.AnnualPremium Between 1000 And 2000) ' 
			END
		ELSE IF @AnnualPremiumAmount = 2001
			BEGIN
				Set @strWhere = @strWhere + ' AND (CR.AnnualPremium Between 2001 And 3000) ' 
			END
		ELSE IF @AnnualPremiumAmount = 3001
			BEGIN
				Set @strWhere = @strWhere + ' AND (CR.AnnualPremium Between 3001 And 4000) ' 
			END
		ELSE
			BEGIN
				Set @strWhere = @strWhere + ' AND (CR.AnnualPremium > 4000) ' 
			END
	END	

declare @strOrderBy varchar(30)
IF @OrderBy = 1 
	BEGIN
		Set @strOrderBy = 'S.ScenarioID'
	END
ELSE IF @OrderBy = 2 
	BEGIN
		Set @strOrderBy = 'PC.CompanyName'
	END
ELSE IF @OrderBy = 3 
	BEGIN
		Set @strOrderBy = 'CR.AnnualPremium '
	END
ELSE IF @OrderBy = 4 
	BEGIN
		Set @strOrderBy = 'CR.Excess '
	END
ELSE IF @OrderBy = 5 
	BEGIN
		Set @strOrderBy = 'PCC.ProviderCoverTypeDesc '
	END
ELSE IF @OrderBy = 6 
	BEGIN
		Set @strOrderBy = 'CM.Description '
	END

--Select (@strSQL + @strWhere) 
IF(@IsFirstTime = 0)
EXEC (@strSQL + @strWhere + ' ORDER BY ' + @strOrderBy) 


--Update tblCarModelInfo Set Status = 1 
--Update tblCarMakeInfo Set Status = 1
--Where CarMakeId = 2001 OR 
--CarMakeId = 802 OR CarMakeId = 1401 OR 
--CarMakeId = 803 OR 
--CarMakeId = 1302 OR 
--CarMakeId = 201

--Select * from tblCarmakeInfo
----Select * from tblCarModelInfo
--Where (CarMakeId = 2001 And CarModelId = 7) OR
--(CarMakeId = 2001 And CarModelId = 7) OR
--(CarMakeId = 2001 And CarModelId = 21) OR
--(CarMakeId = 2001 And CarModelId = 11) OR
--(CarMakeId = 2001 And CarModelId = 13) OR
--(CarMakeId = 802 And CarModelId = 28) OR
--(CarMakeId = 802 And CarModelId = 10) OR
--(CarMakeId = 802 And CarModelId = 34) OR
--(CarMakeId = 802 And CarModelId = 19) OR
--(CarMakeId = 1401 And CarModelId = 14) OR
--(CarMakeId = 1401 And CarModelId = 29) OR
--(CarMakeId = 1401 And CarModelId = 2) OR
--(CarMakeId = 1401 And CarModelId = 27) OR
--(CarMakeId = 803 And CarModelId = 13) OR
--(CarMakeId = 803 And CarModelId = 15) OR
--(CarMakeId = 803 And CarModelId = 19) OR
--(CarMakeId = 803 And CarModelId = 22) OR
--(CarMakeId = 1302 And CarModelId = 8) OR
--(CarMakeId = 1302 And CarModelId = 14) OR
--(CarMakeId = 1302 And CarModelId = 11) OR
--(CarMakeId = 201 And CarModelId = 4) OR
--(CarMakeId = 201 And CarModelId = 30) OR
--(CarMakeId = 201 And CarModelId = 23) 